package com.dms.modules.order.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.dms.modules.order.entity.Order;
import com.dms.modules.order.vo.OrderVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 订单Mapper接口
 */
@Mapper
public interface OrderMapper extends BaseMapper<Order> {
    
    /**
     * 获取用户订单列表
     * @param page 分页参数
     * @param userId 用户ID
     * @param status 订单状态
     * @return 分页订单列表
     */
    IPage<Order> getOrdersByUserId(Page<Order> page, @Param("userId") Long userId, @Param("status") Integer status);
    
    /**
     * 根据ID列表查询订单
     * @param ids 订单ID列表
     * @return 订单列表
     */
    List<Order> selectByIds(@Param("ids") List<Long> ids);
    
    /**
     * 更新订单状态
     * @param orderId 订单ID
     * @param status 订单状态
     * @return 受影响行数
     */
    @Update("UPDATE `order` SET status = #{status}, update_time = NOW() WHERE id = #{orderId}")
    int updateOrderStatus(@Param("orderId") Long orderId, @Param("status") Integer status);
    
    /**
     * 取消订单
     * @param orderId 订单ID
     * @param cancelReason 取消原因
     * @return 受影响行数
     */
    @Update("UPDATE `order` SET status = 4, cancel_reason = #{cancelReason}, update_time = NOW() WHERE id = #{orderId} AND status = 0")
    int cancelOrder(@Param("orderId") Long orderId, @Param("cancelReason") String cancelReason);
    
    /**
     * 确认收货
     * @param orderId 订单ID
     * @return 受影响行数
     */
    @Update("UPDATE `order` SET status = 3, receive_time = NOW(), update_time = NOW() WHERE id = #{orderId} AND status = 2")
    int confirmReceipt(@Param("orderId") Long orderId);
    
    /**
     * 获取商品销售趋势
     * @param productId 商品ID
     * @param startDate 开始时间
     * @param endDate 结束时间
     * @return 销售趋势数据
     */
    @Select("SELECT DATE(create_time) as date, COUNT(*) as count, SUM(total_amount) as amount " +
            "FROM order_item oi " +
            "JOIN orders o ON oi.order_id = o.id " +
            "WHERE oi.product_id = #{productId} " +
            "AND o.create_time BETWEEN #{startDate} AND #{endDate} " +
            "AND o.status = 1 " +
            "GROUP BY DATE(create_time) " +
            "ORDER BY date")
    List<Map<String, Object>> selectProductSalesTrend(
            @Param("productId") Long productId,
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate);
    
    /**
     * 获取商品用户行为数据
     * @param productId 商品ID
     * @return 用户行为数据
     */
    @Select("SELECT u.id as userId, u.username, " +
            "COUNT(DISTINCT o.id) as orderCount, " +
            "SUM(oi.quantity) as totalQuantity, " +
            "SUM(oi.price * oi.quantity) as totalAmount " +
            "FROM order_item oi " +
            "JOIN orders o ON oi.order_id = o.id " +
            "JOIN users u ON o.user_id = u.id " +
            "WHERE oi.product_id = #{productId} " +
            "AND o.status = 1 " +
            "GROUP BY u.id, u.username " +
            "ORDER BY totalAmount DESC")
    List<Map<String, Object>> selectProductUserBehavior(@Param("productId") Long productId);
    
    /**
     * 获取分类分析数据
     * @param categoryId 分类ID
     * @return 分类分析数据
     */
    @Select("SELECT p.id as productId, p.name as productName, " +
            "COUNT(DISTINCT o.id) as orderCount, " +
            "SUM(oi.quantity) as totalQuantity, " +
            "SUM(oi.price * oi.quantity) as totalAmount " +
            "FROM order_item oi " +
            "JOIN orders o ON oi.order_id = o.id " +
            "JOIN products p ON oi.product_id = p.id " +
            "WHERE p.category_id = #{categoryId} " +
            "AND o.status = 1 " +
            "GROUP BY p.id, p.name " +
            "ORDER BY totalAmount DESC")
    List<Map<String, Object>> selectCategoryAnalytics(@Param("categoryId") Long categoryId);
    
    /**
     * 获取搜索热度排行
     * @param limit 返回数量
     * @return 搜索热度数据
     */
    @Select("SELECT p.id as productId, p.name as productName, " +
            "COUNT(DISTINCT s.id) as searchCount " +
            "FROM search_history s " +
            "JOIN products p ON s.product_id = p.id " +
            "WHERE s.create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) " +
            "GROUP BY p.id, p.name " +
            "ORDER BY searchCount DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> selectSearchHotRanking(@Param("limit") Integer limit);

    /**
     * 查询商品销售统计数据
     * @return 包含totalSales（总销量）和totalRevenue（总销售额）的统计数据
     */
    @Select("SELECT " +
            "COUNT(DISTINCT o.id) as totalOrders, " +
            "SUM(oi.quantity) as totalSales, " +
            "SUM(oi.price * oi.quantity) as totalRevenue " +
            "FROM `order` o " +
            "JOIN order_item oi ON o.id = oi.order_id " +
            "WHERE o.status = 4") // 4表示已完成的订单
    List<Map<String, Object>> selectProductSalesAnalytics();

    /**
     * 获取商家店铺名称
     * @param merchantId 商家ID
     * @return 店铺名称
     */
    @Select("SELECT shop_name FROM merchant WHERE id = #{merchantId}")
    String getMerchantShopName(Long merchantId);

    /**
     * 统计商家已完成订单数
     * @param merchantId 商家ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 已完成订单数
     */
    @Select("SELECT COUNT(*) FROM `order` o " +
            "JOIN order_item oi ON o.id = oi.order_id " +
            "WHERE oi.merchant_id = #{merchantId} " +
            "AND o.status = 3 " + // 3-已完成
            "AND o.create_time BETWEEN #{startTime} AND #{endTime}")
    Integer countCompletedOrders(@Param("merchantId") Long merchantId,
                               @Param("startTime") LocalDateTime startTime,
                               @Param("endTime") LocalDateTime endTime);

    /**
     * 统计商家总订单数
     * @param merchantId 商家ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 总订单数
     */
    @Select("SELECT COUNT(*) FROM `order` o " +
            "JOIN order_item oi ON o.id = oi.order_id " +
            "WHERE oi.merchant_id = #{merchantId} " +
            "AND o.create_time BETWEEN #{startTime} AND #{endTime}")
    Integer countTotalOrders(@Param("merchantId") Long merchantId,
                           @Param("startTime") LocalDateTime startTime,
                           @Param("endTime") LocalDateTime endTime);

    /**
     * 统计今日订单数
     */
    @Select("SELECT COUNT(*) FROM `order` o " +
            "WHERE o.merchant_id = #{merchantId} " +
            "AND o.create_time BETWEEN #{startTime} AND #{endTime} " +
            "AND o.deleted = 0")
    Integer countTodayOrders(@Param("merchantId") Long merchantId,
                           @Param("startTime") LocalDateTime startTime,
                           @Param("endTime") LocalDateTime endTime);

    /**
     * 统计今日订单金额
     */
    @Select("SELECT COALESCE(SUM(o.total_amount), 0) FROM `order` o " +
            "WHERE o.merchant_id = #{merchantId} " +
            "AND o.create_time BETWEEN #{startTime} AND #{endTime} " +
            "AND o.deleted = 0")
    BigDecimal sumTodayAmount(@Param("merchantId") Long merchantId,
                             @Param("startTime") LocalDateTime startTime,
                             @Param("endTime") LocalDateTime endTime);

    /**
     * 统计待处理订单数
     */
    @Select("SELECT COUNT(*) FROM `order` o " +
            "WHERE o.merchant_id = #{merchantId} " +
            "AND o.status = 1 " +  // 1表示待发货
            "AND o.deleted = 0")
    Integer countPendingOrders(@Param("merchantId") Long merchantId);

    /**
     * 获取热销商品TOP N
     */
    @Select("SELECT p.id, p.name, " +
            "COUNT(DISTINCT o.id) as orderCount, " +
            "SUM(oi.quantity) as totalQuantity, " +
            "SUM(oi.price * oi.quantity) as totalAmount " +
            "FROM order_item oi " +
            "JOIN `order` o ON oi.order_id = o.id " +
            "JOIN product p ON oi.product_id = p.id " +
            "WHERE o.merchant_id = #{merchantId} " +
            "AND o.status >= 1 " +  // 已支付的订单
            "AND o.deleted = 0 " +
            "GROUP BY p.id, p.name " +
            "ORDER BY totalQuantity DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getHotProducts(@Param("merchantId") Long merchantId,
                                           @Param("limit") Integer limit);

    @Select("SELECT COUNT(DISTINCT o.id) as totalOrders, " +
            "SUM(oi.quantity) as totalSales, " +
            "SUM(oi.price * oi.quantity) as totalRevenue " +
            "FROM `order` o " +
            "JOIN order_item oi ON o.id = oi.order_id " +
            "WHERE o.merchant_id = #{merchantId} AND o.status = 4")
    Map<String, Object> getMerchantSalesStats(@Param("merchantId") Long merchantId);

    @Select("SELECT COUNT(DISTINCT o.id) as totalOrders, " +
            "SUM(oi.quantity) as totalSales, " +
            "SUM(oi.price * oi.quantity) as totalRevenue " +
            "FROM `order` o " +
            "JOIN order_item oi ON o.id = oi.order_id " +
            "WHERE o.merchant_id = #{merchantId} " +
            "AND o.status = 4 " +
            "AND o.create_time BETWEEN #{startTime} AND #{endTime}")
    Map<String, Object> getMerchantSalesStatsByDateRange(
            @Param("merchantId") Long merchantId,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime);

    /**
     * 按天获取商家销售趋势
     */
    @Select("SELECT DATE(o.create_time) as date, " +
            "COUNT(DISTINCT o.id) as orderCount, " +
            "SUM(o.total_amount) as salesAmount, " +
            "SUM(oi.quantity) as salesQuantity " +
            "FROM `order` o " +
            "JOIN order_item oi ON o.id = oi.order_id " +
            "WHERE o.merchant_id = #{merchantId} " +
            "AND o.status >= 1 " +
            "AND o.create_time BETWEEN #{startTime} AND #{endTime} " +
            "AND o.deleted = 0 " +
            "GROUP BY DATE(o.create_time) " +
            "ORDER BY date")
    List<Map<String, Object>> getMerchantSalesTrendByDay(
            @Param("merchantId") Long merchantId,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime);

    /**
     * 按月获取商家销售趋势
     */
    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m') as date, " +
            "COUNT(*) as orderCount, " +
            "SUM(total_amount) as salesAmount, " +
            "SUM(total_quantity) as salesQuantity " +
            "FROM `order` " +
            "WHERE merchant_id = #{merchantId} " +
            "AND status >= 1 " +  // 已支付的订单
            "AND create_time BETWEEN #{startTime} AND #{endTime} " +
            "AND deleted = 0 " +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m') " +
            "ORDER BY date")
    List<Map<String, Object>> getMerchantSalesTrendByMonth(
            @Param("merchantId") Long merchantId,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime);

    /**
     * 获取每日订单完成率数据
     */
    @Select("SELECT " +
            "DATE(o.create_time) as date, " +
            "COUNT(CASE WHEN o.status = 3 THEN 1 END) as completed_orders, " +
            "COUNT(*) as total_orders " +
            "FROM `order` o " +
            "JOIN order_item oi ON o.id = oi.order_id " +
            "WHERE oi.merchant_id = #{merchantId} " +
            "AND o.create_time BETWEEN #{startTime} AND #{endTime} " +
            "AND o.deleted = 0 " +
            "GROUP BY DATE(o.create_time) " +
            "ORDER BY date")
    List<Map<String, Object>> getDailyOrderCompletionRate(
            @Param("merchantId") Long merchantId,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime);
} 